﻿






--获取公司员工档案
CREATE PROCEDURE [dbo].[proc_Employee_GetCompanyList]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@docount bit,
	@username nvarchar (50),
	@unitId nvarchar(4),            --单位
	@departmentId varchar(8000),   --部门
	@grade nvarchar(4),             --职级 
	@state nvarchar(10),             --状态
	@sex nvarchar(4),		--性别
	@name nvarchar(50)	        --姓名
)	

as

--公司Id
declare @companyid varchar (4)
set @companyid = (select companyid from aspnet_Users where username=@username)

--设置--

if(@companyid = '6')
	set @companyid =@unitId

--姓名
if(@name = '')
	set @name = '%'

	
declare @sql_Count1 nvarchar(4000)
declare @sql_Count2 nvarchar(4000)
declare @sql1 nvarchar (4000)
declare @sql2 nvarchar (4000)

set nocount on
	if(@docount=1)
		begin
		set @sql_Count1 =
			+ ' select *,1 as CompanyName,2 as DepartmentName,3 as PostName,4 as GradeName,5 as PostCategoryName'
			+ ' from employee'
			+ ' where'  
				+'  CompanyId like '''+@companyid+''''
				+' and departmentid in ('+@departmentId+')'
				+' and GradeId like '''+@grade+''''
				--+' and State like '''+@state+''''
				+' and State in ('+@state+')'
				+' and Sex like '''+@sex+''''
				+' and EName like ''%'+@name+'%'''
		set @sql_Count2 =
			+ ' select *,1 as CompanyName,2 as DepartmentName,3 as PostName,4 as GradeName,5 as PostCategoryName'
			+ ' from employee'
			+ ' where'  
				+'  CompanyId like '''+@companyid+''''
				+' and departmentid in ('+@departmentId+')'
				+' and GradeId like '''+@grade+''''
				--+' and State like '''+@state+''''
				+' and Sex like '''+@sex+''''
				+' and EName like ''%'+@name+'%'''
				+' and State <> 5'
		if(@state = '%')
			exec (@sql_Count2)
		else
			exec (@sql_Count1)
		end
	else
	begin

	set @sql1 = 
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' set rowcount ' + @endIndex
		+ ' insert into @indextable (nid)'
		+ ' select id '
		+ ' from employee'
				+ ' where'  
				+'  CompanyId like '''+@companyid+''''
				+' and departmentid in ('+@departmentId+')'
				+' and GradeId like '''+@grade+''''
				--+' and State like '''+@state+''''
				+' and State in ('+@state+')'
				+' and Sex like '''+@sex+''''
				+' and EName like ''%'+@name+'%'''
		+'order by  enumber asc'

		+' select' 
		+' E.id'
		+' ,E.UserName'
		+' ,E.EName'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.Sex'
		+' ,E.CompanyId'
		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
		+' ,E.DepartmentId'
		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'		
		+' ,E.State'
		+' ,E.PostId'
		+' ,(select PostName from Post where id=E.PostId) as PostName'
		+' ,E.GradeId'
		+' ,(select Title from Grade where id=E.GradeId) as GradeName'
		+' ,(select Title from Post_Category where id=E.PostCategory) as PostCategoryName'
		+' ,E.WageNumber'
		+' ,E.RuzhiDate'
		+' ,E.LizhiDate'
		+' ,E.Birthday'
		+' ,E.Rtx'
		+' ,E.OfficePhone'
		+' ,E.HomePhone'
		+' ,E.MobilePhone'
		+' ,E.Email'
		+' ,E.IDcard'
		+' ,E.Address'
		+' ,E.Education'
		+' ,E.HeadPic'
		+' ,E.UpName'
		+' ,E.UpTime'
		+' ,E.ZhiWu'		
	+' from employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'
	+' where' 
		+' t.id between '+ @startIndex +' and '+ @endIndex 
	--+' order by t.id'

	set @sql2 = 
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' set rowcount ' + @endIndex
		+ ' insert into @indextable (nid)'
		+ ' select id'
		+ ' from employee'
				+ ' where'  
				+'  CompanyId like '''+@companyid+''''
				+' and departmentid in ('+@departmentId+')'
				+' and GradeId like '''+@grade+''''
				--+' and State like '''+@state+''''
				+' and Sex like '''+@sex+''''
				+' and EName like ''%'+@name+'%'''
				+' and State <> 5'
		+'order by  enumber asc'



		+' select' 
		+' E.id'
		+' ,E.UserName'
		+' ,E.EName'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.Sex'
		+' ,E.CompanyId'
		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
		+' ,E.DepartmentId'
		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'		
		+' ,E.State'
		+' ,E.PostId'
		+' ,(select PostName from Post where id=E.PostId) as PostName'
		+' ,E.GradeId'
		+' ,(select Title from Grade where id=E.GradeId) as GradeName'
		+' ,(select Title from Post_Category where id=E.PostCategory) as PostCategoryName'
		+' ,E.WageNumber'
		+' ,E.RuzhiDate'
		+' ,E.LizhiDate'
		+' ,E.Birthday'
		+' ,E.Rtx'
		+' ,E.OfficePhone'
		+' ,E.HomePhone'
		+' ,E.MobilePhone'
		+' ,E.Email'
		+' ,E.IDcard'
		+' ,E.Address'
		+' ,E.Education'
		+' ,E.HeadPic'
		+' ,E.UpName'
		+' ,E.UpTime'
		+' ,E.ZhiWu'		
	+' from employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'
	+' where' 
		+' t.id between '+ @startIndex +' and '+ @endIndex 
	--+' order by t.id'
		  

--print (@sql)
if(@state = '%')
	exec (@sql2)

else
	exec (@sql1)

--	  print (@sql1)
--print (@sql2)
--exec (@sql)
	end
set nocount off
RETURN






